package com.kaikeba.dao.impl;

import com.kaikeba.bean.Express;
import com.kaikeba.dao.BaseExpressDao;
import com.kaikeba.exception.DuplicateCodeException;
import com.kaikeba.util.DruidUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @description:
 * @author: ManolinCoder
 * @time: 2021/8/30
 */
public class ExpressDaoMysql implements BaseExpressDao {

    //用于查询数据库中的全部快递（总数+新增）和待取件快递（总数+新增）
    //其中如何确定是新增快递？将快递放入时间和现在的时间作比较相等就是当前新增快递
    public static final String SQL_CONSOLE = "SELECT " +
            "COUNT(ID) data1_size," +
            "COUNT(TO_DAYS(INTIME)=TO_DAYS(NOW()) OR NULL) data1_day," +
            "COUNT(STATUS=0 OR NULL) data2_size," +
            "COUNT(TO_DAYS(INTIME)=TO_DAYS(NOW()) AND STATUS=0 OR NULL) data2_day" +
            " FROM EXPRESS";

    //用于查询所有快递(未分页)
    public static final String SQL_FIND_ALL = "SELECT * FROM EXPRESS";
    //用于查询所有快递(分页)
    public static final String SQL_FIND_LIMIT = "SELECT * FROM EXPRESS LIMIT ?,?";
    //根据快递取件码，查询快递信息
    public static final String SQL_FIND_BY_CODE = "SELECT * FROM EXPRESS WHERE CODE=?";
    //根据快递单号，查询快递信息
    public static final String SQL_FIND_BY_NUMBER = "SELECT * FROM EXPRESS WHERE NUMBER=?";
    //根据快递id，查询快递信息
    public static final String SQL_FIND_BY_ID= "SELECT * FROM EXPRESS WHERE ID=?";
    //根据录入人的手机号码，查询录入的所有记录
    public static final String SQL_FIND_BY_SYSPHONE = "SELECT * FROM EXPRESS WHERE SYSPHONE=?";
    //根据用户的手机号码，查询他所有的快递信息
    public static final String SQL_FIND_BY_USERPHONE = "SELECT * FROM EXPRESS WHERE USERPHONE=?";
    //根据单号和用户的手机号码，查询相应记录
    public static final String SQL_FIND_BY_NUMBER_AND_USERPHONE = "SELECT * FROM EXPRESS WHERE NUMBER=? AND USERPHONE=?";
    //根据单号和录入人的手机号码，查询相应记录
    public static final String SQL_FIND_BY_NUMBER_AND_SYSPHONE = "SELECT * FROM EXPRESS WHERE NUMBER=? AND SYSPHONE=?";
    //根据用户的手机号码和状态，查询他所有的快递信息
    public static final String SQL_FIND_BY_USERPHONE_AND_STATUS = "SELECT * FROM EXPRESS WHERE USERPHONE=? AND STATUS=?";
    //快递的录入
    public static final String SQL_INSERT = "INSERT INTO EXPRESS (NUMBER,USERNAME,USERPHONE,COMPANY,CODE,INTIME,STATUS,SYSPHONE) VALUES(?,?,?,?,?,NOW(),0,?)";
    //快递的修改
    public static final String SQL_UPDATE = "UPDATE EXPRESS SET NUMBER=?,USERNAME=?,USERPHONE=?,COMPANY=?,STATUS=? WHERE ID=?";
    //更改快递的状态为1,表示取件
    public static final String SQL_UPDATE_STATUS = "UPDATE EXPRESS SET STATUS=1,OUTTIME=NOW(),CODE=NULL WHERE CODE=?";
    //根据id，删除单个快递信息
    public static final String SQL_DELETE = "DELETE FROM EXPRESS WHERE ID=?";

    /**
     * @return [{size:总数,day:新增},{size:总数,day:新增}]
     * @Author LC
     * @Description //TODO 用于查询数据库中的全部快递（总数+新增）和待取件快递（总数+新增）
     * @Date 19:59 2021/8/30
     * @Param
     **/
    @Override
    public List<Map<String, Integer>> console() {
        ArrayList<Map<String,Integer>> data = new ArrayList<>();
        //1.获取数据库链接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        //2.预编译SQL语句
        try{
            state = conn.prepareStatement(SQL_CONSOLE);
            //3.填充参数（可选）

            //4.执行SQL语句
            result = state.executeQuery();
            //5.获取执行结果
            if (result.next()){
                int data1_size = result.getInt("data1_size");
                int data1_day = result.getInt("data1_day");
                int data2_size = result.getInt("data2_size");
                int data2_day = result.getInt("data2_day");
                Map data1 = new HashMap();
                data1.put("data1_size",data1_size);
                data1.put("data1_day",data1_day);
                Map data2 = new HashMap();
                data2.put("data2_size",data2_size);
                data2.put("data2_day",data2_day);
                data.add(data1);
                data.add(data2);
            }
            //6.资源的释放
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,result);
        }

        return data;
    }

    /**
     * @param limit
     * @param offset
     * @param pageNumber
     * @return 快递的集合
     * @Author LC
     * @Description //TODO 用于查询所有快递
     * @Date 20:02 2021/8/30
     * @Param [limit 是否分页的标记，true表示分页。false表示查询所有快递, offset SQL语句的起始索引, pageNumber 每一页查询的数量]
     */
    @Override
    public List<Express> findAll(boolean limit, int offset, int pageNumber) {
        ArrayList<Express> data = new ArrayList<>();
        //1.获取数据库链接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        //2.预编译SQL语句
        try{
            if (limit){
                state = conn.prepareStatement(SQL_FIND_LIMIT);
                //3.填充参数（可选）
                state.setInt(1,offset);
                state.setInt(2,pageNumber);
            }else {
                state = conn.prepareStatement(SQL_FIND_ALL);
            }
            //4.执行SQL语句
            result = state.executeQuery();
            //5.获取执行结果
            while (result.next()){
                int id = result.getInt("id");
                String number = result.getString("number");
                String username = result.getString("username");
                String userPhone = result.getString("userPhone");
                String company = result.getString("company");
                String code = result.getString("code");
                Timestamp inTime = result.getTimestamp("intime");
                Timestamp outTime = result.getTimestamp("outtime");
                int status = result.getInt("status");
                String sysPhone = result.getString("sysPhone");
                Express e = new Express(id,number,username,userPhone,company,code,inTime,outTime,status,sysPhone);
                data.add(e);
            }
            //6.资源的释放
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,result);
        }

        return data;
    }

    /**
     * @param number
     * @return 查询的快递信息，单号不存在时返回null
     * @Author LC
     * @Description //TODO 根据快递单号，查询快递信息
     * @Date 20:11 2021/8/30
     * @Param [number 单号]
     */
    @Override
    public Express findByNumber(String number) {
        //1.获取数据库链接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        //2.预编译SQL语句
        try{
            state = conn.prepareStatement(SQL_FIND_BY_NUMBER);
            //3.填充参数（可选）
            state.setString(1,number);
            //4.执行SQL语句
            result = state.executeQuery();
            //5.获取执行结果
            if (result.next()){
                int id = result.getInt("id");
                String username = result.getString("username");
                String userPhone = result.getString("userPhone");
                String company = result.getString("company");
                String code = result.getString("code");
                Timestamp inTime = result.getTimestamp("inTime");
                Timestamp outTime = result.getTimestamp("outTime");
                int status = result.getInt("status");
                String sysPhone = result.getString("sysPhone");
                Express e = new Express(id,number,username,userPhone,company,code,inTime,outTime,status,sysPhone);
                return e;
            }
            //6.资源的释放
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,result);
        }
        return null;
    }

    /**
     * @param id
     * @return 查询的快递信息，id不存在时返回null
     * @Author LC
     * @Description //TODO 根据快递id，查询快递信息
     * @Date 19:19 2021/9/12
     * @Param [id 快递的唯一标识]
     */
    @Override
    public Express findById(int id) {
        //1.获取数据库链接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        //2.预编译SQL语句
        try{
            state = conn.prepareStatement(SQL_FIND_BY_ID);
            //3.填充参数（可选）
            state.setInt(1,id);
            //4.执行SQL语句
            result = state.executeQuery();
            //5.获取执行结果
            if (result.next()){
                String number = result.getString("number");
                String username = result.getString("username");
                String userPhone = result.getString("userPhone");
                String company = result.getString("company");
                String code = result.getString("code");
                Timestamp inTime = result.getTimestamp("inTime");
                Timestamp outTime = result.getTimestamp("outTime");
                int status = result.getInt("status");
                String sysPhone = result.getString("sysPhone");
                Express e = new Express(id,number,username,userPhone,company,code,inTime,outTime,status,sysPhone);
                return e;
            }
            //6.资源的释放
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,result);
        }
        return null;
    }

    /**
     * @param code
     * @return 查询的快递信息，取件码不存在时返回null
     * @Author LC
     * @Description //TODO 根据快递取件码，查询快递信息
     * @Date 20:11 2021/8/30
     * @Param [code 取件码]
     */
    @Override
    public Express findByCode(String code) {
        //1.获取数据库链接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        //2.预编译SQL语句
        try{
            state = conn.prepareStatement(SQL_FIND_BY_CODE);
            //3.填充参数（可选）
            state.setString(1,code);
            //4.执行SQL语句
            result = state.executeQuery();
            //5.获取执行结果
            if (result.next()){
                int id = result.getInt("id");
                String number = result.getString("number");
                String username = result.getString("username");
                String userPhone = result.getString("userPhone");
                String company = result.getString("company");
                Timestamp inTime = result.getTimestamp("inTime");
                Timestamp outTime = result.getTimestamp("outTime");
                int status = result.getInt("status");
                String sysPhone = result.getString("sysPhone");
                Express e = new Express(id,number,username,userPhone,company,code,inTime,outTime,status,sysPhone);
                return e;
            }
            //6.资源的释放
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,result);
        }
        return null;
    }

    /**
     * @param userPhone
     * @return 查询的快递信息列表
     * @Author LC
     * @Description //TODO 根据用户的手机号码，查询他所有的快递信息
     * @Date 20:11 2021/8/30
     * @Param [userPhone 手机号码]
     */
    @Override
    public List<Express> findByUserPhone(String userPhone) {
        ArrayList<Express> data = new ArrayList<>();
        //1.获取数据库链接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        //2.预编译SQL语句
        try{
            state = conn.prepareStatement(SQL_FIND_BY_USERPHONE);
            //3.填充参数（可选）
            state.setString(1,userPhone);
            //4.执行SQL语句
            result = state.executeQuery();
            //5.获取执行结果
            while (result.next()){
                int id = result.getInt("id");
                String number = result.getString("number");
                String username = result.getString("username");
                String company = result.getString("company");
                String code = result.getString("code");
                Timestamp inTime = result.getTimestamp("inTime");
                Timestamp outTime = result.getTimestamp("outTime");
                int status = result.getInt("status");
                String sysPhone = result.getString("sysPhone");
                Express e = new Express(id,number,username,userPhone,company,code,inTime,outTime,status,sysPhone);
                data.add(e);
            }
            //6.资源的释放
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,result);
        }
        return data;
    }

    /**
     * @param number
     * @param userPhone
     * @param sysPhone
     * @return 查询的快递信息
     * @Author LC
     * @Description //TODO 根据单号和用户的手机号码或录入人的电话号码，查询快递信息
     * @Date 14:09 2021/9/14
     * @Param [number 快递单号, userPhone 用户的手机号, sysPhone 录入人的电话号码]
     */
    @Override
    public Express findByNumberAndPhone(String number, String userPhone, String sysPhone) {
        //1.获取数据库链接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        //2.预编译SQL语句
        try{
            if (sysPhone == null){
                state = conn.prepareStatement(SQL_FIND_BY_NUMBER_AND_USERPHONE);
                //3.填充参数
                state.setString(1,number);
                state.setString(2,userPhone);
            }else if (userPhone == null){
                state = conn.prepareStatement(SQL_FIND_BY_NUMBER_AND_SYSPHONE);
                //3.填充参数
                state.setString(1,number);
                state.setString(2,sysPhone);
            }
            //4.执行SQL语句
            result = state.executeQuery();
            //5.获取执行结果
            if (result.next() && sysPhone==null){
                int id = result.getInt("id");
                String username = result.getString("username");
                String company = result.getString("company");
                String code = result.getString("code");
                Timestamp inTime = result.getTimestamp("inTime");
                Timestamp outTime = result.getTimestamp("outTime");
                int status = result.getInt("status");
                String sysPhone1 = result.getString("sysPhone");
                Express e = new Express(id,number,username,userPhone,company,code,inTime,outTime,status,sysPhone1);
                return e;
            }
            //再执行一次SQL语句，刷新result索引下标
            result = state.executeQuery();
            if (result.next() && userPhone==null){
                int id = result.getInt("id");
                String username = result.getString("username");
                String userPhone1 = result.getString("userphone");
                String company = result.getString("company");
                String code = result.getString("code");
                Timestamp inTime = result.getTimestamp("inTime");
                Timestamp outTime = result.getTimestamp("outTime");
                int status = result.getInt("status");
                Express e = new Express(id,number,username,userPhone1,company,code,inTime,outTime,status,sysPhone);
                return e;
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            //6.资源的释放
            DruidUtil.close(conn,state,result);
        }
        return null;
    }

    /**
     * @param userPhone
     * @param status
     * @return 查询的快递信息列表
     * @Author LC
     * @Description //TODO 根据用户的手机号码和快递的status，查询他相应status的快递信息
     * @Date 20:11 2021/8/30
     * @Param [userPhone 手机号码]
     */
    @Override
    public List<Express> findByUserPhoneAndStatus(String userPhone, int status) {
        ArrayList<Express> data = new ArrayList<>();
        //1.获取数据库链接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        //2.预编译SQL语句
        try{
            state = conn.prepareStatement(SQL_FIND_BY_USERPHONE_AND_STATUS);
            //3.填充参数（可选）
            state.setString(1,userPhone);
            state.setInt(2,status);
            //4.执行SQL语句
            result = state.executeQuery();
            //5.获取执行结果
            while (result.next()){
                int id = result.getInt("id");
                String number = result.getString("number");
                String username = result.getString("username");
                String company = result.getString("company");
                String code = result.getString("code");
                Timestamp inTime = result.getTimestamp("inTime");
                Timestamp outTime = result.getTimestamp("outTime");
                String sysPhone = result.getString("sysPhone");
                Express e = new Express(id,number,username,userPhone,company,code,inTime,outTime,status,sysPhone);
                data.add(e);
            }
            //6.资源的释放
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,result);
        }
        return data;
    }

    /**
     * @param sysPhone
     * @return 查询的快递信息列表
     * @Author LC
     * @Description //TODO 根据录入人的手机号码，查询录入的所有记录
     * @Date 20:11 2021/8/30
     * @Param [sysPhone 手机号码]
     */
    @Override
    public List<Express> findBySysPhone(String sysPhone) {
        ArrayList<Express> data = new ArrayList<>();
        //1.获取数据库链接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        //2.预编译SQL语句
        try{
            state = conn.prepareStatement(SQL_FIND_BY_SYSPHONE);
            //3.填充参数（可选）
            state.setString(1,sysPhone);
            //4.执行SQL语句
            result = state.executeQuery();
            //5.获取执行结果
            while (result.next()){
                int id = result.getInt("id");
                String number = result.getString("number");
                String username = result.getString("username");
                String company = result.getString("company");
                String userPhone = result.getString("userPhone");
                String code = result.getString("code");
                Timestamp inTime = result.getTimestamp("inTime");
                Timestamp outTime = result.getTimestamp("outTime");
                int status = result.getInt("status");
                Express e = new Express(id,number,username,userPhone,company,code,inTime,outTime,status,sysPhone);
                data.add(e);
            }
            //6.资源的释放
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,result);
        }
        return data;
    }

    /**
     * @param e
     * @return 录入的结果, true表示成功, false表示失败
     * @Author LC
     * @Description //TODO 快递的录入
     * @Date 20:19 2021/8/30
     * @Param [e 要录入的快递对象]
     */
    @Override
    public boolean insert(Express e) throws DuplicateCodeException{
        //1.获取数据库链接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        //2.预编译SQL语句
        try {
            state = conn.prepareStatement(SQL_INSERT);
            //3.填充参数
            state.setString(1,e.getNumber());
            state.setString(2,e.getUsername());
            state.setString(3,e.getUserPhone());
            state.setString(4,e.getCompany());
            state.setString(5,e.getCode());
            state.setString(6,e.getSysPhone());
            //4.执行SQL语句，并获取执行结果
            return state.executeUpdate()>0?true:false;
        }catch (SQLException e1){
//            System.out.println(e1.getMessage());
            if (e1.getMessage().endsWith("for key 'code'")){
                //因为取件码重复出现了异常
                DuplicateCodeException e2 = new DuplicateCodeException(e1.getMessage());
                throw e2;
            }else {
                e1.printStackTrace();
            }
        }finally {
            //5.释放资源
            DruidUtil.close(conn,state,null);
        }
        return false;
    }

    /**
     * @param id
     * @param newExpress
     * @return 修改的结果，true表示成功，false表示失败
     * @Author LC
     * @Description //TODO 快递的修改
     * @Date 20:22 2021/8/30
     * @Param [id 要修改额快递id, newExpress 新的快递对象(number,company,username,userPhone)]
     */
    @Override
    public boolean update(int id, Express newExpress) {
        //1.获取数据库链接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        //2.预编译SQL语句
        try {
            state = conn.prepareStatement(SQL_UPDATE);
            state.setString(1,newExpress.getNumber());
            state.setString(2,newExpress.getUsername());
            state.setString(3,newExpress.getUserPhone());
            state.setString(4,newExpress.getCompany());
            state.setInt(5,newExpress.getStatus());
            state.setInt(6,id);
            return state.executeUpdate()>0?true:false;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //5.释放资源
            DruidUtil.close(conn,state,null);
        }
        return false;
    }

    /**
     * @param code
     * @return 修改的结果，true表示成功，false表示失败
     * @Author LC
     * @Description //TODO 更改快递的状态为1,表示取件完成
     * @Date 20:26 2021/8/30
     * @Param [code 要修改的快递的取件码]
     */
    @Override
    public boolean updateStatus(String code) {
        //1.获取数据库链接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        //2.预编译SQL语句
        try {
            state = conn.prepareStatement(SQL_UPDATE_STATUS);
            state.setString(1,code);
            return state.executeUpdate()>0?true:false;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //5.释放资源
            DruidUtil.close(conn,state,null);
        }
        return false;
    }

    /**
     * @param id
     * @return 删除的结果，true表示成功，false表示失败
     * @Author LC
     * @Description //TODO 根据id，删除单个快递信息
     * @Date 20:27 2021/8/30
     * @Param [id 要删除的快递id]
     */
    @Override
    public boolean delete(int id) {
        //1.获取数据库链接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        //2.预编译SQL语句
        try {
            state = conn.prepareStatement(SQL_DELETE);
            state.setInt(1,id);
            return state.executeUpdate()>0?true:false;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //5.释放资源
            DruidUtil.close(conn,state,null);
        }
        return false;
    }
}
